﻿#region Using Directives

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

#endregion

namespace GeekRule.ORM.Query
{
    /// <summary>
    /// Microsoft SQL Server query adapter
    /// </summary>
    public class MsSqlQueryAdapter : QueryAdapterBase
    {
        #region Private Members

        private static Dictionary<string, List<ParameterDefinition>> _parameterCache = new Dictionary<string, List<ParameterDefinition>>();
        private volatile object _syncRoot = new object();

        #endregion

        #region Constructors

        public MsSqlQueryAdapter(SqlConnection connection) : base(connection)
        {
        }

        public MsSqlQueryAdapter(SqlConnection connection, SqlTransaction transaction) : base(connection, transaction)
        {
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// Executes a query and returns results as a DataSet
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public override DataSet ExecuteDataSet(DbCommand command)
        {
            DataSet dataSet = new DataSet();

            PrepareCommand(command);

            SqlDataAdapter dataAdapter = (SqlDataAdapter) GetDataAdapter((SqlCommand) command);
            dataAdapter.Fill(dataSet);

            return dataSet;
        }

        /// <summary>
        /// Creates an instance of SqlCommand
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public override DbCommand GetCommand(CommandType commandType, string commandText, params object[] values)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;

            if (values != null && values.Length > 0)
            {
                if (commandType == CommandType.StoredProcedure)
                {
                    AutoAssignParameterValues(command, values);
                }
                else
                {
                    AutoAssignCommandTextCommandValues(command, values);
                }
            }

            return command;
        }

        #endregion

        #region Overrides

        /// <summary>
        /// Adds a new parameter to the command parameters with the defined value
        /// </summary>
        /// <param name="command"></param>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        protected override void AddParameterByValue(DbCommand command, string parameterName, object value)
        {
            ((SqlCommand)command).Parameters.AddWithValue(parameterName, value);
        }

        #endregion

        #region Private Helpers

        /// <summary>
        /// Creates an instance of SqlDataAdapter
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        private DataAdapter GetDataAdapter(SqlCommand command)
        {
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            return dataAdapter;
        }

        /// <summary>
        /// Auto-assigns values to command parameters
        /// </summary>
        /// <param name="command"></param>
        /// <param name="values"></param>
        private void AutoAssignParameterValues(SqlCommand command, object[] values)
        {
            if (command.Parameters.Count > 0)
            {
                throw new ApplicationException("command already has parameters assigned");
            }

            string commandText = command.CommandText.ToLower();
            List<ParameterDefinition> parameters = GetParametersForRoutine(commandText);

            int parameterCount = parameters.Count;
            if (parameterCount > 0 && values == null)
            {
                throw new ApplicationException("No values to assign " + commandText + " parameters [" + parameters.Count + " total]");
            }
            else if (parameterCount > 0 && parameterCount != values.Length)
            {
                throw new ApplicationException(commandText + " parameter count [" + parameterCount + "] does not equal the number of values [" + values.Length + "]");
            }
            else if (parameterCount == 0 && values.Length > 0)
            {
                throw new ApplicationException(commandText + " has no defined parameters");
            }
            else
            {
                for (int i = 0; i < parameters.Count; i++)
                {
                    command.Parameters.AddWithValue(parameters[i].Name, (values[i] ?? DBNull.Value));
                }
            }
        }

        /// <summary>
        /// Retrieves MsSql routine parameter definitions
        /// </summary>
        /// <param name="routineName"></param>
        /// <returns></returns>
        private List<ParameterDefinition> GetParametersForRoutine(string routineName)
        {
            // TODO: scrub routineName for full-qualified or "call" prefixed query
            if (!_parameterCache.ContainsKey(routineName))
            {
                string query = string.Format(
                    MsSqlQueryPatterns.GetQueryPattern(QueryStatement.GetParametersForRoutine),
                    routineName
                );

                DbCommand command = GetCommand(CommandType.Text, query);

                using (IDataReader reader = ExecuteReader(command))
                {
                    List<ParameterDefinition> parameterList = new List<ParameterDefinition>();

                    while (reader.Read())
                    {
                            ParameterDefinition parameterDefinition = new ParameterDefinition();
                            parameterDefinition.Name = Convert.ToString(reader["name"]);
                            parameterDefinition.Position = Convert.ToInt32(reader["parameter_id"]);
                            parameterDefinition.Direction = (Convert.ToBoolean(reader["is_output"]) ? ParameterDirection.InputOutput : ParameterDirection.Input);
                            parameterDefinition.DataType = Convert.ToString(reader["system_type_id"]);

                            parameterList.Add(parameterDefinition);
                    }

                    if (parameterList.Count > 0)
                    {
                        lock (_syncRoot)
                        {
                            if (!_parameterCache.ContainsKey(routineName))
                            {
                                _parameterCache.Add(routineName, parameterList);
                            }
                        }
                    }
                }
            }

            if (!_parameterCache.ContainsKey(routineName))
            {
                throw new ApplicationException("Unable to retrieve parameters for routine");
            }

            return _parameterCache[routineName];
        }

        /// <summary>
        /// Retrieve the MsSql database version
        /// </summary>
        /// <returns></returns>
        private int[] GetDatabaseVersion()
        {
            string query = MsSqlQueryPatterns.GetQueryPattern(QueryStatement.GetDatabaseVersion);

            DbCommand command = GetCommand(CommandType.Text, query);

            using (IDataReader reader = ExecuteReader(command))
            {
                if (reader.Read())
                {
                    string version = Convert.ToString(reader[0]);
                    if (!string.IsNullOrEmpty(version))
                    {
                        //TODO: regex match version string to get version:
                        //  Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)   Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
                        string[] versionParts = version.Split('.');
                        int[] versionNumber = new int[versionParts.Length];
                        for (int i = 0; i < versionParts.Length; i++)
                        {
                            versionNumber[i] = Convert.ToInt32(versionParts[i]);
                        }

                        return versionNumber;
                    }
                }
            }

            return new int[0];
        }

        #endregion
    }
}
